﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using BTN04.DTO;
using System.Collections.Generic;
namespace BTN04.DAO
{
    public class LoaiSanPhamDAO
    {
        public List<LoaiSanPhamDTO> DanhSachLoaiSanPham()
        {
            List<LoaiSanPhamDTO> _danhSach = new List<LoaiSanPhamDTO>();
            string sql = "SELECT* FROM LOAISANPHAM Where MaTinhTrangLoaiSanPham=1 ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand comand = new SqlCommand(sql,cnn);
            cnn.Open();
            SqlDataReader read =comand.ExecuteReader();
            while (read.Read())
            {
                LoaiSanPhamDTO spDTO = new LoaiSanPhamDTO();
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
                _danhSach.Add(spDTO);
            }
            cnn.Close();
            return _danhSach;
        }

        //
        public LoaiSanPhamDTO LayLoaiSanPhamTheoMa(int mlsp)
        {
            string sql = "SELECT* FROM LOAISANPHAM Where MaTinhTrangLoaiSanPham=1 And MaLoaiSanPham=" + mlsp.ToString();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand comand = new SqlCommand(sql, cnn);
            cnn.Open();
            SqlDataReader read = comand.ExecuteReader();
            LoaiSanPhamDTO spDTO = new LoaiSanPhamDTO();
            while (read.Read())
            {
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
            }
            cnn.Close();
            return spDTO;
        }

        //Hien thi chi tiet danh sach loai san pham
        public List<LoaiSanPhamDTO> DanhSachChiTietLoaiSanPham()
        {
            List<LoaiSanPhamDTO> _danhSach = new List<LoaiSanPhamDTO>();
            string sql = "SELECT MaLoaiSanPham,TenLoaiSanPham,trlsp.TenTinhTrangLoaiSanPham FROM LoaiSanPham lsp ,TinhTrangLoaiSanPham trlsp where lsp.MaTinhTrangLoaiSanPham=trlsp.MaTinhTrangLoaiSanPham";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand comand = new SqlCommand(sql, cnn);
            cnn.Open();
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                LoaiSanPhamDTO lspDTO = new LoaiSanPhamDTO();
                lspDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                lspDTO.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
                lspDTO.TenTinhTrangLoaiSanPham = read["TenTinhTrangLoaiSanPham"].ToString();
                _danhSach.Add(lspDTO);
            }
            cnn.Close();
            return _danhSach;

        }

        public LoaiSanPhamDTO HienThiChiTietLoaiSanPhamTheoMa(int ma)
        {
            LoaiSanPhamDTO lspDto = new LoaiSanPhamDTO();
            string sql = "SELECT MaLoaiSanPham,TenLoaiSanPham,trlsp.TenTinhTrangLoaiSanPham FROM LoaiSanPham lsp ,TinhTrangLoaiSanPham trlsp where lsp.MaTinhTrangLoaiSanPham=trlsp.MaTinhTrangLoaiSanPham and MaLoaiSanPham=" + ma;
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand comand = new SqlCommand(sql, cnn);
            cnn.Open();
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {

                lspDto.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                lspDto.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
                lspDto.TenTinhTrangLoaiSanPham = read["TenTinhTrangLoaiSanPham"].ToString();

            }
            cnn.Close();
            return lspDto;
        }

        //Them 1 loai san pham
        public int ThemMotLoaiSanPham(LoaiSanPhamDTO lsp)
        {
            string sql = string.Format("Insert into LoaiSanPham(TenLoaiSanPham,MaLoaiTinhTrangSanPham) values('{0}',{1})", lsp.TenLoaiSanPham, lsp.MaLoaiTinhTrangSanPham);
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand(sql, cnn);
            int kq = cmd.ExecuteNonQuery();
            cnn.Close();
            return kq;

        }
        //Xoa loai san pham
        public int XoaMotLoaiSanPham(int ma)
        {
            string sql = "Update LoaiSanPham set MaTinhTrangLoaiSanPham=2 where MaLoaiSanPham= " + ma;
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand(sql, cnn);
            int kq = cmd.ExecuteNonQuery();
            cnn.Close();
            return kq;

        }
        //Cap nhat loai san pham
        public int CapNhatThongTinLoaiSanPham(LoaiSanPhamDTO lsp)
        {
            string sql = "Update LoaiSanPham set TenLoaiSanPham='" + lsp.TenLoaiSanPham + "',MaTinhTrangLoaiSanPham=" + lsp.MaLoaiTinhTrangSanPham + " where MaLoaiSanPham= " + lsp.MaLoaiSanPham;
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            SqlCommand cmd = new SqlCommand(sql, cnn);
            int kq = cmd.ExecuteNonQuery();
            cnn.Close();
            return kq;

        }

    }
}
